package com.api.gbhg.huangwm.web;

import com.alibaba.fastjson.JSON;
import com.customization.commons.LogTool;
import com.engine.common.util.ParamUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import weaver.conn.RecordSet;
import weaver.general.GCONST;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.*;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.util.*;

/**
 * @Author huangwm
 * @Date 2022-04-18
 * @Version 1.0
 * @Description TODO 预算执行承诺书导出
 */
@Path("/gb/huangwm/expBudgetCommitment")
public class ExportBudgetCommitmentApi {
    private LogTool logTool = new LogTool("/log/gbhg/huangwm/web/ExportBudgetCommitment",false);

    private HSSFCellStyle titleStyle = null;//大标题行字体
    private HSSFCellStyle cellStyle = null;//标题行字体
    private HSSFCellStyle dataRowStyle = null;//数据行字体
    private HSSFCellStyle dataRowStyle_o1 = null;//数据行其他字体
    private HSSFCellStyle l3RowStyle = null;//说明所在行

    @GET
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String expExcel(@Context HttpServletResponse response, @Context HttpServletRequest request) throws IOException {
    //public String expExcel(){
        Map<String,Object> result = new HashMap<>();
        try {
            Map<String, Object> requestParams = ParamUtil.request2Map(request);
            RecordSet rs = new RecordSet();
            String ysnd = requestParams.get("ysnd").toString();
            String sbdw = requestParams.get("sbdw").toString();
            //String ysnd="2022",sbdw="31";
            if(ysnd.isEmpty() || sbdw.isEmpty()){
                result.put("apiStatus",false);
                result.put("msg","参数ysnd和sbdw存在空值");
                return JSON.toJSONString(result);
            }
            String sql = "select ysnd,dlhsdw,xmmin,sfwzjxm,yszje,hrm.lastname as jbr,hsb1.subcompanyname as sbdw,hsb2.subcompanyname as szdlhsdw from uf_jingfei_version vs\n" +
                    "left join hrmresource hrm on vs.jbr=hrm.id\n" +
                    "left join hrmsubcompany hsb1 on vs.sbdw=hsb1.id\n" +
                    "left join hrmsubcompany hsb2 on vs.dlhsdw=hsb2.id\n" +
                    "where lcztz=3 and ysnd="+ysnd+" and sbdw="+sbdw;
            logTool.writeLog("查询sql："+sql);
            HSSFWorkbook workbook = new HSSFWorkbook();//创建表格
            HSSFSheet sheet = workbook.createSheet("部门预算承诺书");//在表格中创建一个标签
            //sheet.protectSheet("123456");//加锁
            //设置默认的列宽度
            sheet.setDefaultColumnWidth(9);
            sheet.setColumnWidth(0,250*10);
            //sheet.setColumnWidth(1,350*10);
            sheet.setColumnWidth(1,450*10);
            sheet.setColumnWidth(2,320*10);
            sheet.setColumnWidth(3,500*10);
            sheet.setColumnWidth(4,1400*10);
            sheet.setColumnWidth(5,450*10);

            /*-------------------------大标题行-------------------------*/
            //定义单元格风格
            titleStyle = workbook.createCellStyle();
            //设置水平居中
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font_title = workbook.createFont();
            //设置字体名称
            font_title.setFontName("方正仿宋_GBK");
            //设置字号
            font_title.setFontHeightInPoints((short)14);
            //设置是否为斜体
            font_title.setItalic(false);
            //设置是否加粗
            font_title.setBold(true);
            //设置字体颜色
            font_title.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            titleStyle.setFont(font_title);
            titleStyle.setLocked(true);
            /*--------------------------大标题行-------------------------*/
            HSSFRow row1 = sheet.createRow(0);

            HSSFRow row2 = sheet.createRow(1);//大标题所在行
            HSSFCell cell_row2 = row2.createCell(0);
            cell_row2.setCellStyle(titleStyle);
            cell_row2.setCellValue(ysnd+"年预算执行目标承诺书");
            CellRangeAddress rangeHeard = new CellRangeAddress(1, 1, 0, 6);
            sheet.addMergedRegion(rangeHeard);
            row2.setHeightInPoints(32);

            /*-------------------------小标题行-------------------------*/
            //定义单元格风格
            cellStyle = workbook.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setLocked(true);//锁定行
            //设置字体
            HSSFFont font = workbook.createFont();
            //设置字体名称
            font.setFontName("方正仿宋_GBK");
            //设置字号
            font.setFontHeightInPoints((short)12);
            //设置是否为斜体
            font.setItalic(false);
            //设置是否加粗
            font.setBold(true);
            //设置字体颜色
            font.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            cellStyle.setFont(font);
            /*--------------------------小标题行-------------------------*/
            HSSFRow row3 = sheet.createRow(2);//创建第三行作为标题行
            HSSFCell cell_row3_1 = row3.createCell(0);
            cell_row3_1.setCellStyle(cellStyle);
            cell_row3_1.setCellValue("序号");
//            HSSFCell cell_row3_2 = row3.createCell(1);
//            cell_row3_2.setCellStyle(cellStyle);
//            cell_row3_2.setCellValue("预算年度");
            HSSFCell cell_row3_3 = row3.createCell(1);
            cell_row3_3.setCellStyle(cellStyle);
            cell_row3_3.setCellValue("申报单位");
            HSSFCell cell_row3_4 = row3.createCell(2);
            cell_row3_4.setCellStyle(cellStyle);
            cell_row3_4.setCellValue("经办人");
            HSSFCell cell_row3_5 = row3.createCell(3);
            cell_row3_5.setCellStyle(cellStyle);
            cell_row3_5.setCellValue("独立核算单位");
            HSSFCell cell_row3_6 = row3.createCell(4);
            cell_row3_6.setCellStyle(cellStyle);
            cell_row3_6.setCellValue("项目名称");
            HSSFCell cell_row3_7 = row3.createCell(5);
            cell_row3_7.setCellStyle(cellStyle);
            cell_row3_7.setCellValue("预算金额(元)");
            row3.setHeightInPoints(22);

            /*-------------------------数据行--------------------------*/
            //定义单元格风格
            dataRowStyle = workbook.createCellStyle();
            //设置水平居中
            dataRowStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            dataRowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font_datarow = workbook.createFont();
            //设置字体名称
            font_datarow.setFontName("方正仿宋_GBK");
            //设置字号
            font_datarow.setFontHeightInPoints((short)12);
            //设置是否为斜体
            font_datarow.setItalic(false);
            //设置是否加粗
            font_datarow.setBold(false);
            //设置字体颜色
            font_datarow.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            dataRowStyle.setFont(font_datarow);
            /*-------------------------数据行--------------------------*/
            /*-------------------------数据行其他格式--------------------------*/
            //定义单元格风格
            dataRowStyle_o1 = workbook.createCellStyle();
            //设置水平居中
            dataRowStyle_o1.setAlignment(HorizontalAlignment.LEFT);
            //设置垂直居中
            dataRowStyle_o1.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font_datarow_01 = workbook.createFont();
            //设置字体名称
            font_datarow_01.setFontName("方正仿宋_GBK");
            //设置字号
            font_datarow_01.setFontHeightInPoints((short)12);
            //设置是否为斜体
            font_datarow_01.setItalic(false);
            //设置是否加粗
            font_datarow_01.setBold(false);
            //设置字体颜色
            font_datarow_01.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            dataRowStyle_o1.setFont(font_datarow_01);
            /*-------------------------数据行其他格式--------------------------*/
            int i = 1;
            rs.execute(sql);
            while (rs.next()){
                HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
                //dataRow.setHeightInPoints(20);
                HSSFCell dataRow_c1 = dataRow.createCell(0);//序号
                dataRow_c1.setCellStyle(dataRowStyle);
                dataRow_c1.setCellValue(i+"");
//                HSSFCell dataRow_c2 = dataRow.createCell(1);//预算年度
//                dataRow_c2.setCellStyle(dataRowStyle);
//                dataRow_c2.setCellValue(rs.getString("ysnd"));
                HSSFCell dataRow_c3 = dataRow.createCell(1);//申报单位
                dataRow_c3.setCellStyle(dataRowStyle);
                dataRow_c3.setCellValue(rs.getString("sbdw"));
                HSSFCell dataRow_c4 = dataRow.createCell(2);//经办人
                dataRow_c4.setCellStyle(dataRowStyle);
                dataRow_c4.setCellValue(rs.getString("jbr"));
                HSSFCell dataRow_c5 = dataRow.createCell(3);//独立核算单位
                dataRow_c5.setCellStyle(dataRowStyle);
                dataRow_c5.setCellValue(rs.getString("szdlhsdw"));
                HSSFCell dataRow_c6 = dataRow.createCell(4);//项目名称
                dataRow_c6.setCellStyle(dataRowStyle_o1);
                String sfwzjxm = rs.getString("sfwzjxm");
                dataRow_c6.setCellValue(rs.getString("xmmin")+"(是否追加:"+(sfwzjxm.isEmpty() || sfwzjxm.equals("0")?"否":"是")+")");
                HSSFCell dataRow_c7 = dataRow.createCell(5);//预算总额
                dataRow_c7.setCellStyle(dataRowStyle);
                dataRow_c7.setCellValue(rs.getString("yszje"));
                i++;
            }
            /*-------------------------说明行-------------------------*/
            //定义单元格风格
            l3RowStyle = workbook.createCellStyle();
            //设置水平居中
            l3RowStyle.setAlignment(HorizontalAlignment.LEFT);
            //设置垂直居中
            l3RowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            l3RowStyle.setWrapText(true);
            //设置字体
            HSSFFont font_l3 = workbook.createFont();
            //设置字体名称
            font_l3.setFontName("方正仿宋_GBK");
            //设置字号
            font_l3.setFontHeightInPoints((short)12);
            //设置是否为斜体
            font_l3.setItalic(false);
            //设置是否加粗
            font_l3.setBold(false);
            //设置字体颜色
            font_l3.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            l3RowStyle.setFont(font_l3);
            /*--------------------------说明行-------------------------*/
            sheet.createRow(sheet.getLastRowNum());
            int l3 = sheet.getLastRowNum();
            HSSFRow dataRowLast3 = sheet.createRow(l3+1);
            dataRowLast3.setHeightInPoints(120);
            HSSFCell dataRowLast3_cell = dataRowLast3.createCell(0);
            dataRowLast3_cell.setCellStyle(l3RowStyle);
            dataRowLast3_cell.setCellValue("      \n为强化预算执行主体责任，确保达到总署预算执行要求，本部门(单位)承诺以上经费预算将严格按照要求完成预算执行目标："+
                    (Integer.parseInt(ysnd)-1)+"年结转的中央财政资金原则上在上半年消化完毕，最迟不得晚于9月份，确有特殊情况的需按规定报告后必须在年底前执行完毕；" +
                    ysnd+"年中央财政拨款当年项目预算执行率8月底达到55%以上，年底达到90%以上；关区统筹安排的预算需按序时进度执行，年内执行完毕。\n" +
                    "      特此承诺。\n"+
                    "      党委委员：               部门负责人：             \n" +
                    "      日期：                   日期：             \n"
            );
            CellRangeAddress rangeHeard2 = new CellRangeAddress(l3+1, l3+1, 0, 6);
            sheet.addMergedRegion(rangeHeard2);

            String filename = ysnd+"年"+getSubcompanyInfo(sbdw,"subcompanyname")+"预算执行承诺书"+getTimestamp()+".xls";
            String rootPath = GCONST.getRootPath()+"log\\gbhg\\";
            String path = rootPath + filename;
            //workbook.write(new File(rootPath + filename));//下载文件到服务器
            OutputStream output = response.getOutputStream();
            response.reset();
            //服务器发给浏览器的数据是使用UTF-8编码
            response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");
            //设置浏览器的编码为UTF-8
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename,"UTF-8"));
            workbook.write(output);//该用法需前端配合blob 或者直接open接口地址下载

        }catch (Exception e){
            result.put("apiStatus",false);
            result.put("msg",e.getMessage());
            e.printStackTrace();
            return JSON.toJSONString(result);
        }
        result.put("apiStatus",true);
        result.put("msg","下载成功");
        return JSON.toJSONString(result);
    }

    /**
     * 获取当前日期时间。 YYYY-MM-DD HH:MM:SS
     * @return		当前日期时间
     */
    public String getCurrDateTime() {
        Date newdate = new Date();
        long datetime = newdate.getTime();
        Timestamp timestamp = new Timestamp(datetime);
        return (timestamp.toString()).substring(0, 19);
    }
    /**
     * 获取时间戳   格式如：19990101235959
     * @return
     */
    private String getTimestamp(){
        return getCurrDateTime().replace("-", "").replace(":", "").replace(" ", "");
    }

    /**
     * 根据id和字段名查询分部表字段值
     * @param id
     * @param field
     * @return
     */
    private String getSubcompanyInfo(String id,String field){
        RecordSet rs = new RecordSet();
        rs.execute("select "+field+" from hrmsubcompany where id="+id);
        if(rs.next()){
            return rs.getString(field);
        }
        return "null-未找到";
    }
}
